Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-2041

Trigger should register a dependency on tables and columns used in its body

    XMLWordPrintableJSON

Details

    • Normal
    • Release Note Needed, Repro attached

    Description

      A trigger registers a dependency in the Dependency Manager for those columns which cause the firing of the trigger, but does not register a dependency on tables and columns which are used in the body of the trigger. This means that the trigger may unexpectedly become invalid due to a change to one of those tables and columns in its body, and the user may be surprised that Derby did not warn them of this problem when the underlying table/column change was made.

      An example of this problem is as follows:

      create table t1 (c1 int);
      create table t2 (c2 int);
      create trigger tr1 after update of c1 on t1 for each row mode db2sql insert into t2 (c2) values (1);

      With this set of definitions, Derby will warn/prevent the user about changes to table t1 and column c1, but will not warn the user about changes to table t2 and column c2. If the user drops or renames t2 or c2, the trigger will then give an error the next time it fires.

      It seems like it would be an improvement for the trigger to record this dependency on table t2 and column c2.

      Attachments

        1. d2041-1a.diff
          64 kB
          Knut Anders Hatlen
        2. d2041-1b.diff
          69 kB
          Knut Anders Hatlen
        3. d2041-2a-upgrade-test.diff
          9 kB
          Knut Anders Hatlen
        4. register-dependencies.diff
          20 kB
          Knut Anders Hatlen
        5. releaseNote.html
          3 kB
          Richard N. Hillegas
        6. releaseNote.html
          5 kB
          Knut Anders Hatlen

        Issue Links

          incorporates

          Bug - A problem which impairs or prevents the functions of the product. DERBY-3940 Dropping a column does not drop triggers which mention that column

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. DERBY-4887 ALTER TABLE DROP COLUMN leaves the dependent trigger invalid rather than drop it

          • Major - Major loss of function.
          • Closed
          is related to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-6684 Failure in testDERBY5120NumRowsInSydependsForTrigger when upgrading from 10.11.1.0 to trunk

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-6420 Clarify how DROP statements work on trigger dependencies

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. DERBY-4681 Dropping a column in the table drops the views that use this table

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. DERBY-1632 During revoke privilege, Derby does not look for replacement privilege for the dependent objects and simply drops the dependent objects. This is not SQL compliant and should be fixed.

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. DERBY-1782 When a privilege is revoked at table level, Derby should only drop objects that require that particular privilege and not all the objects that require some form of privilege on that table.

          • Major - Major loss of function.
          • Open

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-1490 Provide ALTER TABLE RENAME COLUMN functionality

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. DERBY-534 Support use of the WHEN clause in CREATE TRIGGER statements

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            People

              knutanders Knut Anders Hatlen
              bryanpendleton Bryan Pendleton
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: